package com.navi.loaddata.loadService.impl.spcData;

import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.pojo.SpcChartDataTmp;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil2;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static java.util.stream.Collectors.groupingBy;

@Service
//@Transactional(transactionManager = "secondaryPlatformTransactionManager",
@Transactional(transactionManager = "mesPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})
public class SplitSpcDataService {
    private static final Logger logger = LoggerFactory.getLogger(SplitSpcDataService.class);
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//yyyy-mm-dd hh:mm:ss   yyyyMMddHHmmss
    public static final String CONTROLDATA_QUERY_COLUMN =" CHARTID ,CONTROLCHARTTYPE ,CHARTOWNER ,FACTORYNAME ,SUBFACTORYNAME ," +
            "     CONTROLDATASEQ ,DCDATAID ,TIMEKEY ,DCTYPE ,CHARTTYPE_EXT ," +
            "     MATERIALNAME ,ALIASITEMNAME ,PRODUCTSPECNAME ,ECCODE ,RUNMODE ,OWNERCODE ,RTC ,QTAP ,EventUser," +
            "     EVENTTIME ,PROCESSOPERATIONNAME ,MACHINENAME ,UNITNAME ,RECIPENAME ," +
            "     EVENTTIME_PRE ,PROCPROCESSOPERATIONNAME ,PROCMACHINENAME ,PROCUNITNAME ,PROCRECIPENAME ," +
            "     USPEC ,TARGET ,LSPEC ,UCL1 ,CL1 ,LCL1 ,UCL2 ,CL2 ,LCL2 ,UCL3 ,CL3 ,LCL3 ," +
            "     D_XBAR ,D_SDEV ,D_RNG ,D_UNF1 ,D_UNF2 ,D_MAX ,D_MIN ,D_X ,D_RM ,D_NP ,D_P ,D_C ,D_U ,DEL_MARK ," +
            "     OOS ,OOC1 ,OOC2 ,OOC3 ,OOR1 ,OOR2 ,OOR3 ,SITECOUNT ,RAWDATAS";

    @Autowired
    private MesRepositoryImpl mes;
    QueryRunner qr_mes = JdbcUtil2.getMesProdRunner();

    public int loadData(EtlConfD etlConfD) throws Exception {
        Session session = mes.getEntityManagerSession();
        logger.info("loadData 开始执行 ！start:{},end:{}",etlConfD.getRunStartTimestamp(),etlConfD.getRunEndTimestamp());
        int count =0;
        String tableNameOfOther="";
        try {
            List<String> tableNameList =getSpcTableNames();
            List<String> tableNameOfOtherList =getSpcOtherTableNames();
            long startTime=System.currentTimeMillis();
                  for( int i=0;i<tableNameOfOtherList.size();i++){
                      tableNameOfOther=tableNameOfOtherList.get(i);
                      count+= getChartDataBySpcOtherTab( etlConfD,session,tableNameOfOther, tableNameList);
                    }
            long endTime=System.currentTimeMillis();
            logger.info("loadData 执行结束 {}ms！,count:{}",endTime-startTime,count);
            return  count;
        } catch (Exception e) {
            logger.error(" #################：tableNameOfOther:{}--start:{}--end:{},error{}",tableNameOfOther,etlConfD.getRunStartTimestamp(),etlConfD.getRunEndTimestamp(), LoggerUtil.stackTraceToString(e));
            throw new Exception(e.getMessage());
        }

    }
    public List<String> getSpcOtherTableNames()throws Exception{
        List<String> list=new ArrayList<>();
        try{
            list =mes.findBySQL(" select t.table_name from user_tables t where t.table_name like 'SPC_CHART_%_OTHER' ");
        }catch (Exception e){
            logger.error(" 获取 SPC_CHART_%_OTHER tableName error {}",e);
            throw new Exception("获取 SPC_CHART_%_OTHER tableName  Exception:"+ e);
        }
        return list;
    }
    public List<String> getSpcTableNames()throws Exception{
        List<String> list=new ArrayList<>();
        try{
            list =mes.findBySQL(" select t.table_name from user_tables t where t.table_name like 'SPC_CHART_%' ");
        }catch (Exception e){
            logger.error(" 获取 SPC tableName error {}",e);
            throw new Exception("获取 SPC tableName  Exception:"+ e);
        }
        return list;
    }
    public int getChartDataBySpcOtherTab(EtlConfD etlConfD, Session session,String tableNameOfOther, List<String> spcTableNames) throws Exception{
        int count =0;
        int[] countArr={0};
        String[] tabArr=tableNameOfOther.split("_");
        List<SpcChartDataTmp> spcChartDataList  = new ArrayList<>();
        try {
            String sql  =" select "+CONTROLDATA_QUERY_COLUMN +
                    " from  "+tableNameOfOther +" CD  where "+
 //                   "  CD.UPDATETIME>to_date('2018-07-01 02:59:29','yyyy-mm-dd hh24:mi:ss')  " +
  //                  " AND  CD.UPDATETIME<to_date('2018-07-01 03:10:29','yyyy-mm-dd hh24:mi:ss')  "+
                    "    CD.UPDATETIME >= to_date('"+sdf.format(etlConfD.getRunStartTimestamp())+"','yyyy-mm-dd hh24:mi:ss')  " +
                    "   AND  CD.UPDATETIME< to_date('"+sdf.format(etlConfD.getRunEndTimestamp())+"','yyyy-mm-dd hh24:mi:ss') " +
                    "    and MACHINENAME is not null  " +
                    "  order by MACHINENAME desc";
            spcChartDataList=  qr_mes.query(sql, new BeanListHandler<SpcChartDataTmp>(SpcChartDataTmp.class));
               if(spcChartDataList.size()<=0) return 0;
            Map<String, List<SpcChartDataTmp>> spcChartDataListTree =
                    spcChartDataList.stream().collect(groupingBy(SpcChartDataTmp::getMachinename));
            spcChartDataListTree.forEach((machineName, spcChartDataTmpByMachinename) -> {
                String tableName="";
                if(tableNameOfOther.contains("_M_")){
                    tableName=tabArr[0]+"_"+tabArr[1]+"_"+tabArr[2]+"_"+tabArr[3]+"_"+machineName.substring(0,4);
                }else{
                    tableName=tabArr[0]+"_"+tabArr[1]+"_"+tabArr[2]+"_"+tabArr[3]+"_"+machineName.substring(0,6);
                }
                if(!spcTableNames.contains(tableName)) return ;
                StringBuffer strbuff =new StringBuffer();
                strbuff.append(" Insert into "+ tableName + " (" + CONTROLDATA_QUERY_COLUMN + ") " );
                strbuff.append(" select "+CONTROLDATA_QUERY_COLUMN +  " from  "+tableNameOfOther +" CD  where  CD.MACHINENAME = '"+machineName+"'" +
                        "  AND  CD.UPDATETIME >= to_date('"+sdf.format(etlConfD.getRunStartTimestamp())+"','yyyy-mm-dd hh24:mi:ss')  " +
                        "   AND  CD.UPDATETIME< to_date('"+sdf.format(etlConfD.getRunEndTimestamp())+"','yyyy-mm-dd hh24:mi:ss') "  );
                SQLQuery queryLoadStep = session.createSQLQuery(strbuff.toString());
                countArr[0] += queryLoadStep.executeUpdate();

                StringBuffer delbuff =new StringBuffer();
                delbuff.append(" delete from  "+tableNameOfOther +" CD  where  CD.MACHINENAME = '"+machineName+"'" +
                        "  AND  CD.UPDATETIME >= to_date('"+sdf.format(etlConfD.getRunStartTimestamp())+"','yyyy-mm-dd hh24:mi:ss')  " +
                        "   AND  CD.UPDATETIME< to_date('"+sdf.format(etlConfD.getRunEndTimestamp())+"','yyyy-mm-dd hh24:mi:ss') "  );
                SQLQuery  queryLoadStep1 = session.createSQLQuery(delbuff.toString());
                int delcount= queryLoadStep1.executeUpdate();

            });
            count =countArr[0];
            return count;

        }catch (SQLIntegrityConstraintViolationException e){
            logger.error("getChartDataBySpcOtherTab(),跳过主键重复数据，tableNameOfOther:{}---{}--{}--{}",tableNameOfOther,etlConfD.getRunStartTimestamp(),etlConfD.getRunEndTimestamp());
        }catch(SQLException e){
            throw new Exception("getChartDataBySpcOtherTab () :"+e+" tableNameOfOther: "+tableNameOfOther);
        }
        return count;
    }

}
